<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

Class Mcustomer extends MY_Model {

    public function __construct() {
        parent::__construct();
    }

    private function formatDateCustom($time) {
        $exp = explode('-', $time);
        list($day, $month, $year) = explode('/', $exp[0]);
        list($_day, $_month, $_year) = explode('/', $exp[1]);
        return array($year . $month . $day, $_year . $_month . $_day);
    }

    public function getCustomer($number = 5, $offset = 0, $time, $id) {

        $top_search = $this->getParamInt('top_search');
        if (!in_array($top_search, array(1, 2))) {
            return $this->stattisticCustomerNormal($number, $offset, $time, $id);
        } else if ($top_search == 1) {
            return $this->stattisticCustomerBandwidth($number, $offset, $time, $id);
        } else if ($top_search == 2) {
            return $this->stattisticCustomerNumberConnect($number, $offset, $time, $id);
        }
    }

    private function stattisticCustomerNormal($number, $offset, $time, $id) {
        //String limit CONCAT(r.`name`,": ",ra.address)
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";

        $where = '';
        $wheretime = '';

        $where2 = '';
        $wheretime2 = '';

        $where3 = '';
        $wheretime3 = '';

        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';

            $where2 .= ' AND radacctdb2.callingstationid like "%' . $smart_search . '%" ';

            $where3 .= ' AND radacctdb2.callingstationid like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
                $wheretime2 = ' AND (DATE_FORMAT(radacctdb2.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb2.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
                $wheretime3 = ' AND (DATE_FORMAT(radacctdb3.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb3.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
                $wheretime2 = ' AND (DATE_FORMAT(radacctdb2.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb2.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
                $wheretime3 = ' AND (DATE_FORMAT(radacctdb3.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb3.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }
        $userList = $this->getIdDevice($id);

        $sql = 'SELECT DISTINCT radacctdb.callingstationid,radacctdb.acctterminatecause,radacctdb.acctsessiontime, radacctdb.acctinputoctets, radacctdb.acctoutputoctets, 
                DATE_FORMAT(radacctdb.acctstarttime, "%H:%i:%s %d/%m/%Y") AS acctstarttime, 
                DATE_FORMAT(radacctdb.acctstoptime, "%H:%i:%s %d/%m/%Y") AS acctstoptime, radacctdb.framedipaddress, cl.denny_client, cl.allow_client, 
                IFNULL(cl.`name`, "No records") AS `name`, IFNULL(cl.osname, "No records") AS osname,
                (SELECT SUM(radacctdb3.acctinputoctets + radacctdb3.acctoutputoctets) AS bw 
                    FROM ' . $this->radius . '.radacct AS radacctdb3 
                    WHERE radacctdb.callingstationid = radacctdb3.callingstationid 
                    AND radacctdb3.username in (' . $userList . ')' . $where3 . $wheretime3 . ' GROUP BY radacctdb3.callingstationid) AS bw
                FROM ' . $this->radius . '.radacct AS radacctdb 
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac 
                WHERE radacctdb.radacctid = 
                    (SELECT max(radacctdb2.radacctid) 
                        FROM ' . $this->radius . '.radacct AS radacctdb2 
                        WHERE radacctdb.callingstationid = radacctdb2.callingstationid 
                        AND radacctdb2.username in (' . $userList . ')' . $where2 . $wheretime2 . '
                        ) AND radacctdb.username in (' . $userList . ')' . $where . $wheretime . '
                  GROUP BY radacctdb.callingstationid  ORDER BY radacctdb.radacctid DESC';

        $xdata = " LIMIT " . $limit . "," . $number . " ";

        $count = $this->query($sql)->num_rows();
        $xs = $this->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count, 'userList' => $userList);
    }

    private function stattisticCustomerBandwidth($number, $offset, $time, $id) {
        //String limit CONCAT(r.`name`,": ",ra.address)
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";

        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }
        $userList = $this->getIdDevice($id);

        $sql = 'SELECT DISTINCT radacctdb.callingstationid,radacctdb.acctterminatecause,radacctdb.acctsessiontime, radacctdb.acctinputoctets, radacctdb.acctoutputoctets, 
                DATE_FORMAT(radacctdb.acctstarttime, "%H:%i:%s %d/%m/%Y") AS acctstarttime, 
                DATE_FORMAT(radacctdb.acctstoptime, "%H:%i:%s %d/%m/%Y") AS acctstoptime, radacctdb.framedipaddress, cl.denny_client, cl.allow_client, 
                IFNULL(cl.`name`, "No records") AS `name`, IFNULL(cl.osname, "No records") AS osname,("no records") as amount,
                (SELECT SUM(radacctdb3.acctinputoctets + radacctdb3.acctoutputoctets) AS bws 
                    FROM ' . $this->radius . '.radacct AS radacctdb3 
                    WHERE radacctdb.callingstationid = radacctdb3.callingstationid 
                    AND radacctdb3.username in (' . $userList . ')' . $where3 . $wheretime3 . ' GROUP BY radacctdb3.callingstationid) AS bw
                FROM ' . $this->radius . '.radacct AS radacctdb 
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac 
                WHERE radacctdb.radacctid = 
                    (SELECT max(radacctdb2.radacctid) 
                        FROM ' . $this->radius . '.radacct AS radacctdb2 
                        WHERE radacctdb.callingstationid = radacctdb2.callingstationid 
                        AND radacctdb2.username in (' . $userList . ')' . $where2 . $wheretime2 . '
                        ) AND radacctdb.username in (' . $userList . ')' . $where . $wheretime . '
                  GROUP BY radacctdb.callingstationid  ORDER BY bw DESC';

        
        $xdata = " LIMIT " . $limit . "," . $number . " ";
        $count = $this->query($sql)->num_rows();
        $xs = $this->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count, 'userList' => $userList);
    }

    private function stattisticCustomerNumberConnect($number, $offset, $time, $id) {
        //String limit CONCAT(r.`name`,": ",ra.address)
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";
        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }
        $userList = $this->getIdDevice($id);
        $sql = 'SELECT DISTINCT radacctdb.callingstationid,radacctdb.acctterminatecause,radacctdb.acctsessiontime, radacctdb.acctinputoctets, radacctdb.acctoutputoctets, 
                DATE_FORMAT(radacctdb.acctstarttime, "%H:%i:%s %d/%m/%Y") AS acctstarttime, 
                DATE_FORMAT(radacctdb.acctstoptime, "%H:%i:%s %d/%m/%Y") AS acctstoptime, radacctdb.framedipaddress, cl.denny_client, cl.allow_client, 
                IFNULL(cl.`name`, "No records") AS `name`, IFNULL(cl.osname, "No records") AS osname,("no records") as bw,
                (SELECT COUNT(radacctdb3.callingstationid) amount
                    FROM ' . $this->radius . '.radacct AS radacctdb3 
                    WHERE radacctdb.callingstationid = radacctdb3.callingstationid 
                    AND radacctdb3.username in (' . $userList . ')' . $where3 . $wheretime3 . ' GROUP BY radacctdb3.callingstationid) AS amount
                FROM ' . $this->radius . '.radacct AS radacctdb 
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac 
                WHERE radacctdb.radacctid = 
                    (SELECT max(radacctdb2.radacctid) 
                        FROM ' . $this->radius . '.radacct AS radacctdb2 
                        WHERE radacctdb.callingstationid = radacctdb2.callingstationid 
                        AND radacctdb2.username in (' . $userList . ')' . $where2 . $wheretime2 . '
                        ) AND radacctdb.username in (' . $userList . ')' . $where . $wheretime . '
                  GROUP BY radacctdb.callingstationid  ORDER BY amount DESC';

        $xdata = " LIMIT " . $limit . "," . $number . " ";
        $count = $this->query($sql)->num_rows();
        $xs = $this->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count, 'userList' => $userList);
    }

    public function getCustomerList($number = 5, $offset = 0, $time) {

        $top_search = $this->getParamInt('top_search');
        if (!in_array($top_search, array(1, 2))) {
            return $this->stattisticCustomerListNormal($number, $offset, $time);
        } else if ($top_search == 1) {
            return $this->stattisticCustomerListBandwidth($number, $offset, $time);
        } else if ($top_search == 2) {
            return $this->stattisticCustomerListNumberConnect($number, $offset, $time);
        }
    }

    private function stattisticCustomerListNormal($number, $offset, $time) {
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";
        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }
        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }
        $sql = 'SELECT  radacctdb.callingstationid,md5(rc.id_device) as deviceid, g.`name` AS gname, d.namedevice,
                        DATE_FORMAT(radacctdb.acctstarttime, "%H:%i:%s %d/%m/%Y") AS acctstarttime, 
                        DATE_FORMAT(radacctdb.acctstoptime, "%H:%i:%s %d/%m/%Y") AS acctstoptime, 
                        SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) AS bw, 
                        radacctdb.framedipaddress, cl.denny_client, cl.allow_client,
                        IFNULL(cl.`name`, "No records") AS `name`,
                        IFNULL(cl.osname,  "No records") AS osname
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }
        $sql .= ' WHERE 1=1' . $where . $wheretime;
        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY d.id,radacctdb.callingstationid ";
        }
        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY d.id,radacctdb.callingstationid ";
        }
        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY radacctdb.radacctid DESC";
        } else {
            $sql .= " GROUP BY radacctdb.callingstationid, radacctdb.username ORDER BY radacctdb.radacctid DESC";
        }

        $xdata = " LIMIT " . $limit . "," . $number . " ";
        $count = $this->query($sql)->num_rows();
        $xs = $this->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }

    private function stattisticCustomerListBandwidth($number, $offset, $time) {
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";
        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }
        $sql = 'SELECT  radacctdb.callingstationid,md5(rc.id_device) as deviceid, g.`name` AS gname, d.namedevice,
                        DATE_FORMAT(radacctdb.acctstarttime, "%H:%i:%s %d/%m/%Y") AS acctstarttime, 
                        DATE_FORMAT(radacctdb.acctstoptime, "%H:%i:%s %d/%m/%Y") AS acctstoptime, 
                        SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) AS bw, 
                        ("no records") as amount,
                        radacctdb.framedipaddress, cl.denny_client, cl.allow_client,
                        IFNULL(cl.`name`, "No records") AS `name`,
                        IFNULL(cl.osname,  "No records") AS osname
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }
        $sql .= ' WHERE 1=1' . $where . $wheretime;
        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY d.id,radacctdb.callingstationid ";
        }
        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY d.id,radacctdb.callingstationid ";
        }
        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) DESC";
        } else {
            $sql .= " GROUP BY radacctdb.callingstationid, radacctdb.username ORDER BY SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) DESC";
        }
        $xdata = " LIMIT " . $limit . "," . $number . " ";
        $count = $this->query($sql)->num_rows();
        $xs = $this->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }

    private function stattisticCustomerListNumberConnect($number, $offset, $time) {
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";
        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }
        $sql = 'SELECT  radacctdb.callingstationid,md5(rc.id_device) as deviceid, g.`name` AS gname, d.namedevice,
                        DATE_FORMAT(radacctdb.acctstarttime, "%H:%i:%s %d/%m/%Y") AS acctstarttime, 
                        DATE_FORMAT(radacctdb.acctstoptime, "%H:%i:%s %d/%m/%Y") AS acctstoptime, 
                        COUNT(radacctdb.callingstationid) AS amount, 
                        ("no records") as bw, 
                        radacctdb.framedipaddress, cl.denny_client, cl.allow_client,
                        IFNULL(cl.`name`, "No records") AS `name`,
                        IFNULL(cl.osname,  "No records") AS osname
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id';


        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }
        $sql .= ' WHERE 1=1' . $where . $wheretime;
        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY d.id,radacctdb.callingstationid ";
        }
        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY d.id,radacctdb.callingstationid ";
        }
        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY COUNT(radacctdb.callingstationid) DESC";
        } else {
            $sql .= " GROUP BY radacctdb.callingstationid, radacctdb.username ORDER BY COUNT(radacctdb.callingstationid) DESC";
        }
        $xdata = " LIMIT " . $limit . "," . $number . " ";
        $count = $this->query($sql)->num_rows();
        $xs = $this->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }

    public function getchartPie($time) {

        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }

        $sql = 'SELECT  g.`name` AS gname,count(DISTINCT radacctdb.callingstationid) AS bw 
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id
                ';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE 1=1' . $where . $wheretime;

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY g.`id` ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY g.`id` ";
        }

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY radacctdb.radacctid DESC";
        } else {
            $sql .= " GROUP BY g.`id` ORDER BY radacctdb.radacctid DESC";
        }
        return $this->query($sql)->result_object();
    }

    public function getchartsList($time) {
        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }

        $sql = 'SELECT DATE_FORMAT(radacctdb.acctstoptime, "%Y-%m-%d") AS acctstoptime,
                        SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) AS bw
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE 1=1 AND radacctdb.acctstoptime IS NOT NULL ' . $where . $wheretime;

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ";
        }

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ASC";
        } else {
            $sql .= " GROUP BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ORDER BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ASC";
        }

        return $this->query($sql)->result_object();
    }

    private function getIdDevice($id) {
        $sql = 'SELECT radcheck2.username
                FROM ' . $this->cpanel . '.tbl_device tbld 
                INNER JOIN ' . $this->cpanel . '.tbl_address AS tbladdress ON tbld.id_address = tbladdress.id 
                INNER JOIN ' . $this->cpanel . '.province AS provincetbl ON tbladdress.id_province = provincetbl.provinceid 
                INNER JOIN ' . $this->cpanel . '.district AS districttbl ON tbladdress.id_district = districttbl.districtid 
                INNER JOIN ' . $this->radius . '.radcheck AS radcheck2  ON tbld.id = radcheck2.id_device WHERE md5(tbld.id) = "' . $id . '"';

        $sql2 = 'SELECT `mac` FROM customer_log WHERE id_device = "' . $id . '"';


        $xs = $this->query($sql)->result_object();
        $xs2 = $this->db->query($sql2)->result_object();

        $var = '';
        foreach ($xs AS $el => $le) {
            $var .= ",'" . $le->username . "'";
        }

        foreach ($xs2 AS $el2 => $le2) {
            $var .= ",'" . $le2->mac . "'";
        }

        return substr($var, 1);
    }

    public function getcharts($id, $time, $userList) {
        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }
        
        $sql = 'SELECT DATE_FORMAT(radacctdb.acctstoptime, "%Y-%m-%d") AS acctstoptime, 
                        SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) AS bw
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                WHERE radacctdb.username in (' . $userList . ') AND radacctdb.acctstoptime IS NOT NULL' . $where . $wheretime . '
                GROUP BY DATE_FORMAT(radacctdb.acctstoptime, "%Y-%m-%d") ORDER BY DATE_FORMAT(radacctdb.acctstoptime, "%Y-%m-%d") ASC';

        return $this->query($sql)->result_object();
    }

    public function getNetWorkGroup() {
        if ($this->session->userdata('level') == 2) {
            $data = $this->db->query('SELECT tg.id, tg.name FROM tbl_group AS tg LEFT JOIN manage_group_key AS gk ON gk.group_id = tg.id WHERE gk.user_id = ' . $this->session->userdata('id'))->result_array();
        } else if ($this->session->userdata('level') == 3) {
            $data = $this->db->query('SELECT tg.id,tg.`name` FROM tbl_group tg INNER JOIN manage_device_key mdk ON tg.id = mdk.group_id WHERE mdk.user_id = ' . $this->session->userdata('id') . ' GROUP BY tg.id')->result_array();
        } else {
            $data = $this->db->query('SELECT id,`name` FROM tbl_group ORDER BY id DESC')->result_array();
        }

        $arr = array();
        $arr[0] = 'Vui lòng chọn nhóm mạng';
        foreach ($data AS $el => $le) {
            $arr[$le['id']] = $le['name'];
        }
        return $arr;
    }

    public function getInfo($idDevice) {
        return $this->db->query('SELECT g.`name` AS gname, d.namedevice,  d.mac, CONCAT(a.address,", ", dt.`name`,", ", p.`name`) AS address FROM tbl_device d INNER JOIN tbl_group g ON d.network_group = g.id INNER JOIN tbl_address a ON d.id_address = a.id INNER JOIN district dt ON a.id_district = dt.districtid INNER JOIN province p ON a.id_province = p.provinceid WHERE md5(d.id) = "' . $idDevice . '"')->row_object();
    }

    public function getprovincedeviceDB($id) {

        $sql = 'SELECT p.`name` AS pname, p.provinceid
                FROM tbl_group g INNER JOIN tbl_device d ON g.id = d.network_group
                INNER JOIN tbl_address ta ON d.id_address = ta.id
                INNER JOIN province p ON ta.id_province = p.provinceid ';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE g.id = ' . $id;

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id');
        }

        $sql .= " GROUP BY p.`name` ORDER BY p.`name` ASC";

        $data = $this->db->query($sql)->result_array();

        $array = array('0' => 'Chọn tỉnh thành');
        foreach ($data AS $el => $le) {
            $array[$le['provinceid']] = $le['pname'];
        }

        return $array;
    }

    public function getdistrictdeviceDB($id, $id_g) {

        $sql = 'SELECT p.`name` AS dname, p.districtid
                FROM tbl_group g INNER JOIN tbl_device d ON g.id = d.network_group
                INNER JOIN tbl_address ta ON d.id_address = ta.id
                INNER JOIN district p ON ta.id_district = p.districtid ';


        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE p.provinceid = ' . $id . ' AND g.id = ' . $id_g;

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ")";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id');
        }

        $sql .= " GROUP BY p.`name` ORDER BY p.`name` ASC";

        $data = $this->db->query($sql)->result_array();
        $array = array('0' => 'Chọn quận huyện');
        foreach ($data AS $el => $le) {
            $array[$le['districtid']] = $le['dname'];
        }

        return $array;
    }

    public function getaddressdevicememberDB($id, $id_g) {
        $data = $this->db->query('SELECT ta.address, ta.id
                                    FROM tbl_group g INNER JOIN tbl_device d ON g.id = d.network_group
                                             INNER JOIN tbl_address ta ON d.id_address = ta.id
                                             INNER JOIN district p ON ta.id_district = p.districtid WHERE p.districtid = ' . $id . ' AND g.id = ' . $id_g . ' GROUP BY p.`name` ORDER BY p.`name` ASC')->result_array();
        $array = array('0' => 'Chọn địa điểm');
        foreach ($data AS $el => $le) {
            $array[$le['id']] = $le['address'];
        }

        return $array;
    }

    public function getlistdeviceaddressmemberDB($id, $id_g) {

        $sql = 'SELECT d.namedevice, d.id
                FROM tbl_group g 
                INNER JOIN tbl_device d ON g.id = d.network_group
                INNER JOIN tbl_address ta ON d.id_address = ta.id
                INNER JOIN district p ON ta.id_district = p.districtid 
                ';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE d.id_address = ' . $id . ' AND g.id = ' . $id_g;

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id');
        }


        $sql .= " ORDER BY p.`name` ASC";


        $data = $this->db->query($sql)->result_array();

        $array = array('0' => 'Chọn thiết bị');
        foreach ($data AS $el => $le) {
            $lf = md5($le['id']);
            $array[$lf] = $le['namedevice'];
        }

        return $array;
    }

    public function getInfoCustomer($iduser, $iddevice) {
        $sql = 'SELECT rd.nasporttype, 
                        rd.acctstarttime, 
                        rd.acctstoptime, 
                        rd.calledstationid, 
                        rd.callingstationid, 
                        rd.framedipaddress, 
                        cl.`name` AS vname, 
                        cl.osname, cl.denny_client, cl.allow_client,
                        d.namedevice, 
                        d.id, rd.acctterminatecause,
                        a.lat, 
                        a.`long`, 
                        a.`name` as aname, 
                        a.address,CONCAT(a.`name`, " - ",a.address) address
                FROM ' . $this->radius . '.radcheck rc 
                INNER JOIN ' . $this->radius . '.radacct rd ON rd.username = rc.username
                LEFT JOIN ' . $this->cpanel . '.customer_log cl ON rd.callingstationid = cl.mac
                INNER JOIN ' . $this->cpanel . '.tbl_device d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_address a ON d.id_address = a.id
                WHERE md5(rc.id_device) = "' . $iddevice . '" AND cl.id_device = "' . $iddevice . '" AND rd.callingstationid = "' . $iduser . '"
                ORDER BY rd.acctsessionid DESC';

        return $this->query($sql)->row_object();
    }

    public function getchartsDetail($time, $mac, $iddevice) {
        $wheretime = '';
        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }

        $sql = 'SELECT DATE_FORMAT(radacctdb.acctstoptime, "%Y-%m-%d") AS acctstoptime, 
                        SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) AS bw
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                WHERE 1=1 AND radacctdb.callingstationid = "' . $mac . '" AND radacctdb.acctstoptime IS NOT NULL' . $wheretime . '
                GROUP BY DATE_FORMAT(radacctdb.acctstoptime, "%Y-%m-%d") ORDER BY DATE_FORMAT(radacctdb.acctstoptime, "%Y-%m-%d") ASC';

        //die($sql);
        return $this->query($sql)->result_object();
    }

    public function savenamedeviceDB($name_edit, $mac, $id) {
        //die('UPDATE customer_log SET `name` = "'.$name_edit.'" WHERE mac = "'.$mac.'" AND md5(id_device) = "'.$id.'"');
        $this->db->query('UPDATE customer_log SET `name` = "' . $name_edit . '" WHERE mac = "' . $mac . '" AND id_device = "' . $id . '"');
    }

    public function dennyallowcustomerdeviceDb($mac, $id, $type) {
        if ($type == 2) {
            $file_config = $this->db->query('SELECT linkupdate,version FROM tbl_device WHERE md5(id) = "' . $id . '"')->row_object();
            $link_game_online = $this->extralfileziptoserver(PROJECT_PATH . '/public/' . $file_config->linkupdate);
            $config = file_get_contents($link_game_online['path'] . '/update/config');
            $exp = explode('HS_MACALLOW="', $config);
            $_st1 = str_replace('HS_MACAUTHMODE=local', "", $exp[0]);
            $st1 = str_replace('HS_MACAUTH=on', "\nHS_MACAUTH=on\nHS_MACAUTHMODE=local ", $_st1);
            $expmac = explode('# List of MAC addresses to authenticate', $exp[1]);
            $dup = 0;
            $currentmac = explode(',', str_replace('..."      ', '', $expmac[0]));
            foreach ($currentmac AS $el) {
                if ($el == $mac) {
                    $dup = 1;
                }
            }
            if ($dup == 1) {
                $_mac = $expmac[0];
            } else {
                $_mac = $mac . ',' . $expmac[0];
            }

            file_put_contents($link_game_online['path'] . '/update/config', $st1 . "\n" . 'HS_MACALLOW="' . str_replace(',...', '', $_mac) . '# List of MAC addresses to authenticate' . $expmac[1]);
            $this->load->library('zip');
            $this->zip->read_dir('public/tmpsconfig/' . $link_game_online['time'] . '/update', FALSE);
            $this->zip->archive('public/' . $file_config->linkupdate);
            $this->db->query('UPDATE tbl_device set version = "' . (($file_config->version) + 1) . '" WHERE md5(id) = "' . $id . '"');
            @unlink(PROJECT_PATH . '/public/tmpsconfig/' . $link_game_online['time']);
        } else {
            $file_config = $this->db->query('SELECT linkupdate,version FROM tbl_device WHERE md5(id) = "' . $id . '"')->row_object();
            $link_game_online = $this->extralfileziptoserver(PROJECT_PATH . '/public/' . $file_config->linkupdate);
            $config = file_get_contents($link_game_online['path'] . '/update/config');
            $exp = explode('HS_MACALLOW="', $config);
            $_st1 = str_replace('HS_MACAUTHMODE=local', "", $exp[0]);
            $st1 = str_replace('HS_MACAUTH=on', "\nHS_MACAUTH=on\nHS_MACAUTHMODE=local ", $_st1);
            $expmac = explode('# List of MAC addresses to authenticate', $exp[1]);
            $currentmac = explode(',', str_replace('"', '', str_replace('..."      ', '', $expmac[0])));
            $macs = '';
            foreach ($currentmac AS $el => $le) {
                if (trim($le) != $mac) {
                    $macs .= ',' . trim($le);
                }
            }
            $macs = substr($macs, 1);

            file_put_contents($link_game_online['path'] . '/update/config', $st1 . "\n" . 'HS_MACALLOW="' . $macs . '" # List of MAC addresses to authenticate' . $expmac[1]);
            $this->load->library('zip');
            $this->zip->read_dir('public/tmpsconfig/' . $link_game_online['time'] . '/update', FALSE);
            $this->zip->archive('public/' . $file_config->linkupdate);
            $this->db->query('UPDATE tbl_device set version = "' . (($file_config->version) + 1) . '" WHERE md5(id) = "' . $id . '"');
            @unlink(PROJECT_PATH . '/public/tmpsconfig/' . $link_game_online['time']);
        }

        $check = $this->db->query('SELECT id FROM customer_log WHERE mac = "' . $mac . '" AND id_device = "' . $id . '"')->num_rows();
        if ($check > 0) {
            if ($type == 1) {
                $arr = 'denny_client = 1, allow_client = 0';
            } else if ($type == 2) {
                $arr = 'denny_client = 0, allow_client = 1';
            } else if ($type == 3) {
                $arr = 'denny_client = 0, allow_client = 0';
            }
            $this->db->query('UPDATE customer_log SET ' . $arr . ' WHERE mac = "' . $mac . '" AND id_device = "' . $id . '"');
        } else {
            if ($type == 1) {
                $arrs = array('mac' => $mac, 'id_device' => $id, 'denny_client' => 1, 'allow_client' => 0);
            } else if ($type == 2) {
                $arrs = array('mac' => $mac, 'id_device' => $id, 'denny_client' => 0, 'allow_client' => 1);
            } else if ($type == 3) {
                $arrs = array('mac' => $mac, 'id_device' => $id, 'denny_client' => 0, 'allow_client' => 0);
            }
            $this->db->insert('customer_log', $arrs);
        }
    }

    /*
     * Denny member
     */

    public function getCustomerListDenny($number = 5, $offset = 0, $time) {

        //String limit CONCAT(r.`name`,": ",ra.address)
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";

        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND (radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%") ';
        }

        @$device_search = $this->getParamString('device_search');
        if (!in_array($device_search, array(null, '', '0'))) {
            $where .= ' AND md5(rc.id_device) =  "' . $device_search . '"';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }

        $sql = 'SELECT radacctdb.callingstationid, md5(rc.id_device) as deviceid, g.`name` AS gname, d.namedevice,
                        DATE_FORMAT(radacctdb.acctstarttime, "%H:%i:%s %d/%m/%Y") AS acctstarttime, 
                        DATE_FORMAT(radacctdb.acctstoptime, "%H:%i:%s %d/%m/%Y") AS acctstoptime, 
                        SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) AS bw, 
                        radacctdb.framedipaddress, cl.denny_client, cl.allow_client,
                        IFNULL(cl.`name`, "No records") AS `name`,
                        IFNULL(cl.osname,  "No records") AS osname
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id';


        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE 1=1' . $where . $wheretime . ' AND cl.denny_client = 1';

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY d.id,radacctdb.callingstationid ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY d.id,radacctdb.callingstationid ";
        }

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY radacctdb.radacctid DESC";
        } else {
            $sql .= " GROUP BY radacctdb.callingstationid, radacctdb.username ORDER BY radacctdb.radacctid DESC";
        }

        $xdata = " LIMIT " . $limit . "," . $number . " ";

        $count = $this->query($sql)->num_rows();
        $xs = $this->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }

    public function getchartsListDenny($time) {
        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }

        $sql = 'SELECT DATE_FORMAT(radacctdb.acctstoptime, "%Y-%m-%d") AS acctstoptime,
                        SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) AS bw
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE 1=1 AND radacctdb.acctstoptime IS NOT NULL ' . $where . $wheretime . ' AND cl.denny_client = 1';

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ";
        }

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ASC";
        } else {
            $sql .= " GROUP BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ORDER BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ASC";
        }

        return $this->query($sql)->result_object();
    }

    public function getchartPieDenny($time) {


        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }

        $sql = 'SELECT  g.`name` AS gname,count(DISTINCT radacctdb.callingstationid) AS bw 
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id
                ';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE 1=1' . $where . $wheretime . ' AND cl.denny_client = 1';

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY g.`id` ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY g.`id` ";
        }

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY radacctdb.radacctid DESC";
        } else {
            $sql .= " GROUP BY g.`id` ORDER BY radacctdb.radacctid DESC";
        }
        return $this->query($sql)->result_object();
    }

    /*
     * Denny member
     */

    public function getCustomerListAllow($number = 5, $offset = 0, $time) {

        //String limit CONCAT(r.`name`,": ",ra.address)
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";

        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND (radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%") ';
        }

        @$device_search = $this->getParamString('device_search');
        if (!in_array($device_search, array(null, '', '0'))) {
            $where .= ' AND md5(rc.id_device) =  "' . $device_search . '"';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }

        $sql = 'SELECT radacctdb.callingstationid, md5(rc.id_device) as deviceid, g.`name` AS gname, d.namedevice,
                        DATE_FORMAT(radacctdb.acctstarttime, "%H:%i:%s %d/%m/%Y") AS acctstarttime, 
                        DATE_FORMAT(radacctdb.acctstoptime, "%H:%i:%s %d/%m/%Y") AS acctstoptime, 
                        SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) AS bw, 
                        radacctdb.framedipaddress, cl.denny_client, cl.allow_client,
                        IFNULL(cl.`name`, "No records") AS `name`,
                        IFNULL(cl.osname,  "No records") AS osname
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id';


        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE 1=1' . $where . $wheretime . ' AND cl.allow_client = 1';

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND id.id IN (" . $id_dv . ") GROUP BY d.id,radacctdb.callingstationid ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY d.id,radacctdb.callingstationid ";
        }

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY radacctdb.radacctid DESC";
        } else {
            $sql .= " GROUP BY radacctdb.callingstationid, radacctdb.username ORDER BY radacctdb.radacctid DESC";
        }

        $xdata = " LIMIT " . $limit . "," . $number . " ";

        $count = $this->query($sql)->num_rows();
        $xs = $this->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }

    public function getchartsListAllow($time) {
        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }

        $sql = 'SELECT DATE_FORMAT(radacctdb.acctstoptime, "%Y-%m-%d") AS acctstoptime,
                        SUM(radacctdb.acctinputoctets + radacctdb.acctoutputoctets) AS bw
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE 1=1 AND radacctdb.acctstoptime IS NOT NULL ' . $where . $wheretime . ' AND cl.allow_client = 1';

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ";
        }

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ASC";
        } else {
            $sql .= " GROUP BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ORDER BY DATE_FORMAT(radacctdb.acctstoptime, '%Y-%m-%d') ASC";
        }

        return $this->query($sql)->result_object();
    }

    public function getchartPieAllow($time) {

        $where = '';
        $wheretime = '';
        $smart_search = $this->getParamString('smart_search');
        if (!in_array($smart_search, array(null, '0', ''))) {
            $where .= ' AND radacctdb.callingstationid like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`name` like "%' . $smart_search . '%" ';
            $where .= ' OR cl.`osname` like "%' . $smart_search . '%" ';
        }

        if (!in_array($time, array(null, '', '0', 'Chọn thời gian'))) {
            $arr_time = $this->formatDateCustom($time);
            if ($arr_time[0] == $arr_time[1]) {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") = ' . $arr_time[0] . ' OR DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") = ' . $arr_time[1] . ')';
            } else {
                $wheretime = ' AND (DATE_FORMAT(radacctdb.acctstarttime, "%Y%m%d") >= ' . $arr_time[0] . ' AND DATE_FORMAT(radacctdb.acctstoptime, "%Y%m%d") <= ' . $arr_time[1] . ')';
            }
        }

        $sql = 'SELECT  g.`name` AS gname,count(DISTINCT radacctdb.callingstationid) AS bw 
                FROM ' . $this->radius . '.radacct AS radacctdb
                LEFT JOIN ' . $this->cpanel . '.customer_log AS cl ON radacctdb.callingstationid = cl.mac
                INNER JOIN ' . $this->radius . '.radcheck AS rc ON radacctdb.username = rc.username
                INNER JOIN ' . $this->cpanel . '.tbl_device as d ON rc.id_device = d.id
                INNER JOIN ' . $this->cpanel . '.tbl_group as g ON d.network_group = g.id
                ';

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " LEFT JOIN " . $this->cpanel . ".manage_device_key mdk ON d.id = mdk.device_id ";
        }

        $sql .= ' WHERE 1=1' . $where . $wheretime . ' AND cl.allow_client = 1';

        if ($this->session->userdata('level') == 2) {
            $group_list = $this->db->query('SELECT group_id FROM manage_group_key WHERE user_id = ' . $this->session->userdata('id'))->result_object();
            $id_gr = '';
            foreach ($group_list AS $el => $le) {
                $id_gr .= ',"' . $le->group_id . '"';
            }
            $id_gr = substr($id_gr, 1);

            $id_device = $this->db->query('SELECT id FROM tbl_device WHERE network_group in (' . $id_gr . ')')->result_object();

            $id_dv = '';
            foreach ($id_device AS $el => $le) {
                $id_dv .= ',"' . $le->id . '"';
            }
            $id_dv = substr($id_dv, 1);

            //$sql .= " AND d.id IN (".$id_dv.") GROUP BY d.id ";
            $sql .= " AND d.id IN (" . $id_dv . ") GROUP BY g.`id` ";
        }

        if ($this->session->userdata('level') == 3) {
            $sql .= " AND mdk.user_id = " . $this->session->userdata('id') . " GROUP BY g.`id` ";
        }

        if (in_array($this->session->userdata('level'), array(2, 3))) {
            $sql .= " ORDER BY radacctdb.radacctid DESC";
        } else {
            $sql .= " GROUP BY g.`id` ORDER BY radacctdb.radacctid DESC";
        }
        return $this->query($sql)->result_object();
    }

}
